package cs304.p3.library.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import cs304.p3.library.domain.Book;
import cs304.p3.library.domain.BookCopy;
import cs304.p3.library.domain.BookCopy.BookStatus;
import cs304.p3.library.domain.Borrowing;
import cs304.p3.library.exceptions.AddBookCopyException;
import cs304.p3.library.exceptions.AddBookException;
import cs304.p3.library.util.SqlUtils;

public class BookDao extends Dao {

	/**
	 * Insert a new Book into the Database.
	 * Also inserts a new Copy of the book (its first copy!)
	 */
	public void insertBook(Book book) throws AddBookException, AddBookCopyException, SQLException {
		Map<String, String> values = Maps.newHashMap();
		values.put("callNumber", book.getCallNumber());
		values.put("isbn", book.getIsbn());
		values.put("title", book.getTitle());
		values.put("mainAuthor", book.getMainAuthor());
		values.put("publisher", book.getPublisher());
		values.put("year", String.valueOf(book.getYear()));

		String sql = SqlUtils.insertSql("Book", values);
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.executeUpdate();
	}

	/**
	 * Inserts another copy of a Book into the Database.
	 */
	public void insertBookCopy(BookCopy copy) throws AddBookCopyException, SQLException {
		Map<String, String> values = Maps.newHashMap();
		values.put("callNumber", copy.getCallNumber());
		values.put("copyNo", String.valueOf(copy.getCopyNo()));
		values.put("status", BookStatus.IN.toString());

		String sql = SqlUtils.insertSql("BookCopy", values);
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.executeUpdate();
	}

	/**
	 * Attaches an Author to a Book.
	 */
	public void insertAuthor(String callNumber, String name) throws SQLException {
		Map<String, String> values = Maps.newHashMap();
		values.put("callNumber", callNumber);
		values.put("name", name);
		String sql = SqlUtils.insertSql("HasAuthor", values);
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.executeUpdate();
	}

	/**
	 * Attaches a Subject to a Book.
	 */
	public void insertSubject(String callNumber, String subject) throws SQLException {
		Map<String, String> values = Maps.newHashMap();
		values.put("callNumber", callNumber);
		values.put("subject", subject);
		String sql = SqlUtils.insertSql("HasSubject", values);
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.executeUpdate();
	}

	public Book selectBook(String callNumber) throws SQLException {
		String sql = "SELECT * FROM Book WHERE callNumber='" + callNumber + "'";
		System.out.println(sql);
		PreparedStatement select = connection.prepareStatement(sql);
		ResultSet rs = select.executeQuery();

		if(rs.next()) {
			Book b = new Book();
			b.setCallNumber(rs.getString("callNumber"));
			b.setIsbn(rs.getString("isbn"));
			b.setTitle(rs.getString("title"));
			b.setMainAuthor(rs.getString("mainAuthor"));
			b.setPublisher(rs.getString("publisher"));
			b.setYear(rs.getInt("year"));
			return b;
		} else {
			// No such book
			return null;
		}
	}

	public List<BookCopy> selectBookCopy(String callNumber, String copyNo, String status)
			throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM BookCopy");
		sql.append(" WHERE ");
		Map<String, String> keyVals = Maps.newHashMap();
		keyVals.put("callNumber", callNumber);
		keyVals.put("copyNo", String.valueOf(copyNo));
		keyVals.put("status", status);
		sql.append(SqlUtils.whereStrVal(keyVals));

		System.out.println(sql.toString());
		PreparedStatement ps = connection.prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();

		List<BookCopy> bookCopies = Lists.newArrayList();
		while(rs.next()) {
			BookCopy bookCopy = new BookCopy();
			bookCopy.setCallNumber(rs.getString("callNumber"));
			bookCopy.setCopyNo(Integer.valueOf(rs.getString("copyNo")));
			bookCopy.setStatus(BookStatus.getBookStatus(rs.getString("status")));
			bookCopies.add(bookCopy);
		}

		return bookCopies;
	}

	public void insertBorrowing(String borid, String bid, String callNumber, int copyNo,
			String outDate, String inDate) throws SQLException {
		Map<String, String> keyVals = Maps.newHashMap();
		keyVals.put("borid", borid);
		keyVals.put("bid", bid);
		keyVals.put("callNumber", callNumber);
		keyVals.put("copyNo", String.valueOf(copyNo));
		keyVals.put("outDate", outDate);
		keyVals.put("inDate", inDate);

		String sql = SqlUtils.insertSql("Borrowing", keyVals);
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.executeUpdate();
	}

	public List<BookCopy> selectCurrentlyBorrowedBooks(String subject) throws SQLException {
		// get all the book copies with OUT status
		// get the borrowing for each copy, to get the dates
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT C.*, B.outDate, B.inDate FROM Borrowing B, BookCopy C ");
		sql.append("WHERE C.callNumber=B.callNumber AND C.status='" + BookStatus.OUT.toString() + "' ");
		if(subject != null && !subject.isEmpty()) {
			// subject is determined by HasSubject table.
			sql.append("AND C.callNumber IN (");
			sql.append("SELECT S.callNumber FROM HasSubject S ");
			sql.append("WHERE S.subject=\"" + subject + "\") ");
		}
		sql.append("ORDER BY C.callNumber");

		System.out.println(sql.toString());
		PreparedStatement ps = connection.prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();

		List<BookCopy> borrowedBooks = Lists.newArrayList();
		while(rs.next()) {
			String callNumber = rs.getString("callNumber");
			int copyNo = rs.getInt("copyNo");
			BookStatus status = BookStatus.getBookStatus(rs.getString("status"));
			Date outDate = rs.getDate("outDate");
			Date inDate = rs.getDate("inDate");
			BookCopy copy = new BookCopy(callNumber, copyNo, status, outDate, inDate);
			borrowedBooks.add(copy);
		}

		return borrowedBooks;
	}

	public List<Borrowing> selectBorrowing() throws SQLException {
		String sql = SqlUtils.selectSql("Borrowing");
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		List<Borrowing> borrowedBooks = Lists.newArrayList();
		while(rs.next()) {
			String borid = rs.getString("borid");
			String bid = rs.getString("bid");
			String callNumber = rs.getString("callNumber");
			int copyNo = rs.getInt("copyNo");
			Date outDate = rs.getDate("outDate");
			Date inDate = rs.getDate("inDate");
			Borrowing b = new Borrowing(borid, bid, callNumber, copyNo, outDate, inDate);
			borrowedBooks.add(b);
		}

		return borrowedBooks;
	}

	public Borrowing selectBorrowing(String borid) throws SQLException {
		Map<String, String> keyVals = Maps.newHashMap();
		keyVals.put("borid", borid);
		List<Borrowing> borrowedBooks = selectBorrowing(keyVals);
		if(borrowedBooks.size() > 0) {
			return borrowedBooks.get(0);
		}
		System.err.println("More than one book with the same borid??");
		return null;
	}

	public List<Borrowing> selectBorrowingFromBorrower(String bid) throws SQLException {
		Map<String, String> keyVals = Maps.newHashMap();
		keyVals.put("bid", bid);
		return selectBorrowing(keyVals);
	}

	public List<Borrowing> selectBorrowingFromBook(String callNumber) throws SQLException {
		Map<String, String> keyVals = Maps.newHashMap();
		keyVals.put("callNumber", callNumber);
		return selectBorrowing(keyVals);
	}

	public List<Borrowing> selectBorrowingFromBookCopy(String callNumber, int copyNo)
			throws SQLException {
		Map<String, String> keyVals = Maps.newHashMap();
		keyVals.put("callNumber", callNumber);
		keyVals.put("copyNo", String.valueOf(copyNo));
		return selectBorrowing(keyVals);
	}

	private List<Borrowing> selectBorrowing(Map<String, String> values) throws SQLException {
		String sql = SqlUtils.selectSql("Borrowing", values);
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		List<Borrowing> borrowedBooks = Lists.newArrayList();
		while(rs.next()) {
			String borid = rs.getString("borid");
			String bid = rs.getString("bid");
			String callNumber = rs.getString("callNumber");
			int copyNo = rs.getInt("copyNo");
			Date outDate = rs.getDate("outDate");
			Date inDate = rs.getDate("inDate");
			Borrowing b = new Borrowing(borid, bid, callNumber, copyNo, outDate, inDate);
			borrowedBooks.add(b);
		}

		return borrowedBooks;
	}

	public List<Book> selectTopBorrowed(int year, int n) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT B.* FROM Book B ");
		sql.append("WHERE B.callNumber IN (");

		// Select the call numbers of the top n books in the year
		Calendar c = Calendar.getInstance();
		c.set(year, 0, 1, 0, 0, 0);
		c.getTime();
		Date start = new Date(c.getTimeInMillis());
		c.set(year+1, 0, 1, 0, 0, 0);
		Date end = new Date(c.getTimeInMillis());

		StringBuilder borYear = new StringBuilder();
		borYear.append("SELECT N.callNumber FROM Borrowing N ");
		borYear.append("WHERE N.outDate>='" + SqlUtils.getDateString(start) + "' ");
		borYear.append("AND N.outDate<'" + SqlUtils.getDateString(end) + "' ");
		borYear.append("GROUP BY N.callNumber ");
		//borYear.append("ORDER BY COUNT(*) DESC ");
		//borYear.append("LIMIT " + n);

		sql.append(borYear);
		sql.append(") ");
		//sql.append("ORDER BY freq DESC");

		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();

		List<Book> books = Lists.newArrayList();
		while(rs.next()) {
			String callNumber = rs.getString("callNumber");
			String isbn = rs.getString("isbn");
			String title = rs.getString("title");
			String mainAuthor = rs.getString("mainAuthor");
			String publisher = rs.getString("publisher");
			int y = rs.getInt("year");
			Book b = new Book(callNumber, isbn, title, mainAuthor, publisher, y);
			books.add(b);
		}
		return books;
	}

	public void updateBorrowing(String borid, String inDate) throws SQLException {
		Map<String, String> updateVals = Maps.newHashMap();
		updateVals.put("inDate", inDate);
		Map<String, String> whereVals = Maps.newHashMap();
		whereVals.put("borid", borid);
		String sql = SqlUtils.updateSql("Borrowing", updateVals, whereVals);
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.executeUpdate();
	}

	public void updateBookCopy(String callNumber, int copyNo, String status) throws SQLException {
		Map<String, String> updateVals = Maps.newHashMap();
		updateVals.put("status", status);
		Map<String, String> whereVals = Maps.newHashMap();
		whereVals.put("callNumber", callNumber);
		whereVals.put("copyNo", String.valueOf(copyNo));
		String sql = SqlUtils.updateSql("BookCopy", updateVals, whereVals);
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.executeUpdate();
	}

	/**
	 * Generate the copy number from the given book
	 * @param callNumber
	 * @return
	 * @throws SQLException
	 */
	public int generateCopyNumber(String callNumber) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT copyNo FROM BookCopy ");
		sql.append("WHERE callNumber='" + callNumber + "' ");
		sql.append("ORDER BY copyNo DESC");

		System.out.println(sql.toString());
		PreparedStatement ps = connection.prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			// first entry is the largest copy number
			return rs.getInt(1) + 1;
		}
		// first copy
		return 1;
	}

	public List<String> selectBook(List<String> titles, List<String> authors, List<String> subjects) throws SQLException {
		String sql = "";

		if(titles.isEmpty() && authors.isEmpty() && subjects.isEmpty()) {
			sql = "SELECT callNumber From Book";
		}
		else {
			if(titles.isEmpty()) 
				titles.add("#######");
			if(authors.isEmpty())
				authors.add("#######");
			if(subjects.isEmpty())
				subjects.add("######");
			
			sql = "SELECT UNIQUE Book.callNumber" +
					" FROM Book, HasSubject, HasAuthor" +
					" WHERE Book.callNumber=HasAuthor.callNumber" +
					" OR Book.callNumber=HasSubject.callNumber" +
					" AND (Book.title IN"+SqlUtils.commaQuoteStrVal(titles) +
					" OR Book.mainAuthor IN"+SqlUtils.commaQuoteStrVal(authors) +
					" OR HasAuthor.name IN"+SqlUtils.commaQuoteStrVal(authors) +
					" OR HasSubject.subject IN"+SqlUtils.commaQuoteStrVal(subjects)+
					")";
		}

		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<String> callNumbers = Lists.newArrayList();
		while(rs.next()) {
			callNumbers.add(rs.getString(1));
		}

		return callNumbers;
	}

	public int[] countInOut(String callNumber) throws SQLException {
		String sql = "SELECT COUNT(*)" +
				" FROM BookCopy" +
				" WHERE BookCopy.callNumber='"+callNumber+"'" +
				" AND BookCopy.status='in'";
		System.out.println(sql);
		PreparedStatement ps = connection.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		int inCount = 0;
		while(rs.next()) {
			inCount = rs.getInt(1);
		}

		String sql2 = "SELECT COUNT(*)" +
				" FROM BookCopy" +
				" WHERE BookCopy.callNumber='"+callNumber+"'" +
				" AND BookCopy.status='out'";
		System.out.println(sql2);
		PreparedStatement ps2 = connection.prepareStatement(sql2);
		ResultSet rs2 = ps2.executeQuery();
		int outCount = 0;
		while(rs.next()) {
			outCount = rs2.getInt(1);
		}

		int[] counts = {inCount,outCount};
		return counts;
	}

}
